/* 
 * @author:Andromeda
 * description:This Notice DAO class which is called by notice related action\
 * request: All Notice related Actions
 * response: According to return type of functions
 */

package com.DAO;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.util.*;
import java.sql.*;


import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.struts.util.RequestUtils;

import com.beans.*;

import com.connection.ConnectionFactory;


public class NoticeDAO extends HttpServlet {
	
	private Connection con=null;
	public NoticeDAO()
	{
		
		con = ConnectionFactory.getConnection();
		
	}
	
	
	@SuppressWarnings("finally")
	public int getNoticeCountByCategory(int cat_id)
	{	
		String query;
		int count=0;
		
		if(cat_id==0){
			 query = "select count(*) from Notice";
		}
		else
		{
			 query = "select count(*) from Notice where category_id="+cat_id;
		}
		
		Statement st = null;
		ResultSet rs = null;
		try 
		{

			st = con.createStatement();
			rs = st.executeQuery(query);
			
			if(rs.next())
			{
				System.out.println("Come in rs.....");
				count=rs.getInt(1);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally 
		{			
			/* close connection */
			try 
			{
				
				if (st != null) 
				{
					st.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				
				e.printStackTrace();
			}
	
			return count;		 
		}
 }
	
	
	@SuppressWarnings("finally")
	public ArrayList<NoticeForm> get()  throws  SQLException
	{	
		ArrayList<NoticeForm> list = new ArrayList<NoticeForm>();
		
		String query = "select *from Notice";
		Statement st = null;
		ResultSet rs = null;
		try 
		{

			st = con.createStatement();
			rs = st.executeQuery(query);
			NoticeForm nf= new NoticeForm();
			while(rs.next())
			{	
				nf.setNoticeId(rs.getInt(1));
				nf.setTitle(rs.getString(2));
				nf.setDescription(rs.getString(3));
				nf.setCreatorId(rs.getString(4));
				nf.setCreatorName(rs.getString(5));
				nf.setCreatedDate(rs.getDate(6));
				nf.setExpirationDate(rs.getDate(7).toString());
				nf.setCategoryId(rs.getInt(8));
				
				list.add(nf);
			}
			
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (st != null) 
				{
					st.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
			return list;				//returnn title , description , creator_id , date & time 
		}
		
}

	@SuppressWarnings("finally")
	public ArrayList<NoticeForm>  getPageData(int pageNo,int categoryType)  throws  SQLException
	{	
		ArrayList<NoticeForm> list = new ArrayList<NoticeForm>();
		String query;
		if(categoryType==0){
				query= "select * from Notice order by created_date desc limit ?,5";
		}
		
		else{
	    query = "select * from Notice where category_id=" +categoryType+
					" order by created_date desc limit ?,5";
		}
			
		PreparedStatement ps = null;
		ResultSet rs = null;
		try 
		{
			int startRecord=pageNo*5;
			ps = con.prepareStatement(query);
			ps.setInt(1,startRecord);
			rs = ps.executeQuery();
			
			
			while(rs.next())
			{	
				NoticeForm nf= new NoticeForm();
				nf.setNoticeId(rs.getInt(1));
				nf.setTitle(rs.getString(2));
				nf.setDescription(rs.getString(3));
				nf.setCreatorId(rs.getString(4));
				nf.setCreatorName(rs.getString(5));
				nf.setCreatedDate(rs.getDate(6));
				nf.setExpirationDate(rs.getDate(7).toString());
				nf.setCategoryId(rs.getInt(8));

				
				list.add(nf);
				
			}
			
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (ps != null) 
				{
					ps.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
			System.out.println("list size in DAO....."+list.size());
			return list;				//returnn title , description , creator_id , date & time 
		}
		
}


/*public ResultSet search(int id) throws SQLException
{	
		
		String query = "select * from Notice where notice_id="+id;
		Statement st = null;
		ResultSet rs = null;
		try 
		{

			st = con.createStatement();
			rs = st.executeQuery(query);
			

		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		} 
		finally 
		{
			
			try 
			{
				ConnectionFactory.closeConnection();
				if (st != null) 
				{
					st.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
			return rs;
		}
		
}*/



public int insert(NoticeForm n) throws SQLException 
{
	// TODO Auto-generated method stub
	String title=n.getTitle();
	String Discription=n.getDescription();
	String creator_id=n.getCreatorId();
	String expirationDate=n.getExpirationDate();
	int cat_id=n.getCategoryId();
	String creator_name=n.getCreatorName();
	
	String query = "INSERT into notice(title,description,creator_id,expiration_date,creator_name,category_id) values(?,?,?,?,?,?);";
	PreparedStatement ps = null;
	int rowsAffected=0;
	try 
	{
		ps=con.prepareStatement(query);
		ps.setString(1,title);
		System.out.println("title......");
		ps.setString(2,Discription);
		System.out.println("description......");
		ps.setString(3,creator_id);
		System.out.println("cretaor id......");
		ps.setString(4, expirationDate);
		System.out.println("tdate......");
		ps.setString(5,creator_name);
		ps.setInt(6,cat_id);
		rowsAffected = ps.executeUpdate();
		System.out.println("updated......");
		
				
	} catch (SQLException e)
		{
		    System.out.println("Error in prepare statement.......................");
			e.printStackTrace();
		} finally 
			{
				
				try 
				{
					if (ps != null) 
					{
						ps.close();
					}
		
				} catch (SQLException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
			}
	
		return rowsAffected;
}

public int delete(int id)  throws SQLException
{
	// TODO Auto-generated method stub
	
	String query = "delete from notice where notice_id=?";
	
	PreparedStatement ps = null;
	int rowsAffected=0;
	
	try {	
			ps=con.prepareStatement(query);
			ps.setInt(1,id);
			rowsAffected = ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (ps != null) 
				{
					ps.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		}
	return rowsAffected;
}


	
	@SuppressWarnings("finally")
	public NoticeForm getNoticeById(int id)
	{
		String query = "select * from Notice where notice_id="+id;
		PreparedStatement ps = null;
		ResultSet rs = null;
		NoticeForm nf= new NoticeForm();
		try 
		{
            Statement stmt=con.createStatement();
			//ps = con.prepareStatement(query);
	        //ps.setInt(1,id);
	        //ps.e
			rs = stmt.executeQuery(query);
			System.out.println("ps executed.......");
			
			if(rs.next())
			{	
				nf.setNoticeId(rs.getInt(1));
				nf.setTitle(rs.getString(2));
				nf.setDescription(rs.getString(3));
				nf.setCreatorId(rs.getString(4));
				nf.setCreatorName(rs.getString(5));
				nf.setCreatedDate(rs.getDate(6));
				nf.setExpirationDate(rs.getDate(7).toString());
				nf.setCategoryId(rs.getInt(8));
				
			}
			
		} catch (SQLException e)
		{
			System.out.println("error in getNotice....");
			e.printStackTrace();
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (ps != null) 
				{
					ps.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
			return nf;				 
		}
		
	}
	
	@SuppressWarnings("finally")
	public ArrayList<NoticeForm> getNoticeByUser(String userid)
	{
		String query = "select * from Notice where creator_id='"+userid+"'";
		ArrayList<NoticeForm> noticelist=new ArrayList<NoticeForm>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try 
		{
            Statement stmt=con.createStatement();
			//ps = con.prepareStatement(query);
	        //ps.setInt(1,id);
	        //ps.e
			rs = stmt.executeQuery(query);
			System.out.println("ps executed.......");
			
			while(rs.next())
			{	
				NoticeForm nf= new NoticeForm();
				nf.setNoticeId(rs.getInt(1));
				nf.setTitle(rs.getString(2));
				nf.setDescription(rs.getString(3));
				nf.setCreatorId(rs.getString(4));
				nf.setCreatorName(rs.getString(5));
				nf.setCreatedDate(rs.getDate(6));
				nf.setExpirationDate(rs.getDate(7).toString());
				nf.setCategoryId(rs.getInt(8));
				noticelist.add(nf);
				
			}
			
		} catch (SQLException e)
		{
			System.out.println("error in getNotice....");
			e.printStackTrace();
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (ps != null) 
				{
					ps.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
			return noticelist;	 
		}
		
	}
	
	
	@SuppressWarnings("finally")
	public ArrayList<NoticeForm> getNoticesByCategory(int id)
	{
		String query = "select * from Notice where category_id="+id;
		ResultSet rs = null;
		ArrayList<NoticeForm> noticeList=new ArrayList<NoticeForm>();
		
		try 
		{
            Statement stmt=con.createStatement();
			rs = stmt.executeQuery(query);
			System.out.println("ps executed.......");
			
			while(rs.next())
			{	
				NoticeForm nf=new NoticeForm();
				nf.setNoticeId(rs.getInt(1));
				nf.setTitle(rs.getString(2));
				nf.setDescription(rs.getString(3));
				nf.setCreatorId(rs.getString(4));
				nf.setCreatorName(rs.getString(5));
				nf.setCreatedDate(rs.getDate(6));
				nf.setExpirationDate(rs.getDate(7).toString());
				nf.setCategoryId(rs.getInt(8));
				
				noticeList.add(nf);
			}
			
			System.out.println("Notice list is...."+noticeList);
			
		} catch (SQLException e)
		{
			System.out.println("error in getNotice....");
			e.printStackTrace();
		} finally 
		{
			/* close connection */
			try 
			{		
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				
				e.printStackTrace();
			}
	
			return noticeList; 
		}
		
	}
	
	
	public ArrayList<CommentForm> getComments(int Notice_id) throws SQLException
	{
		CommentDAO commentDao=new CommentDAO();
		return commentDao.get(Notice_id);
	}
	public void closeConnection()  throws SQLException
	{
		con.close();
		ConnectionFactory.closeConnection();
	}
} 

	

//category_name - id






